import sys
import os
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from typing import Optional


from langchain_core.tools import tool
from mysql.connector import connect
from tools.get_mysql import MySQLConnectionFactory
import random
import datetime
from tools.utility import timer_decorator

mysql_factory = MySQLConnectionFactory()

@tool
@timer_decorator
def after_sales_service(customer_id: int, item_id: int, 
                order_number: str, reason: str,after_sales_type: str) -> str:
    """提供售后服务、退货退款、换货等服务。

    Args:
        customer_id (int): 客户ID
        item_id (int): 商品ID
        order_number (str): 订单编号
        reason (str): 退货原因
        after_sales_type (str): 售后类型，例如（退款退货、换货、退货退款）
    Returns:
        str: 售后服务结果
    """

    user_query = """
    SELECT username,phone_number,address FROM customers WHERE customer_id = %s
    """
    user_info = mysql_factory.execute_query(user_query, (customer_id,))
    ####检查是否满足退货、换货、退款条件
    ####退货条件：订单状态为已发货，且收货地址为当前地址，7天无理由退货
    ####换货条件：订单状态为已发货，且收货地址为当前地址，7天无理由换货
    ####退款条件：订单状态为已发货，且收货地址为当前地址，7天无理由退款
    order_query = """
    SELECT order_id,order_time,product_name FROM orders WHERE customer_id = %s AND item_id = %s AND order_number = %s
    """
    order_info = mysql_factory.execute_query(order_query, (customer_id, item_id, order_number))
    if order_info:
        order_id = order_info[0]['order_id']
        order_time = order_info[0]['order_time']
        if order_time:
            # 如果订单在7天内，允许售后
            if (datetime.datetime.now() - order_time).days > 7:
                return "不满足售后条件，订单已超过7天，不能进行售后处理"            
    else:
        return "订单不存在"
    # 生成售后单号: 当前时间+4位随机数
    after_sales_number = datetime.datetime.now().strftime("%Y%m%d%H%M%S") + str(random.randint(1000,9999)).zfill(4)
    insert_query = """
    INSERT INTO after_sales (
        after_sales_number, after_sales_type, application_time,
        order_id, product_id, product_name, customer_id, customer_name,
        customer_contact, after_sales_reason, problem_description,
        processing_status
    ) VALUES (
        %s, %s, NOW(),
        %s, %s, %s, %s, %s,
        %s, %s, %s,
        %s
    )
    """
    values = (
        after_sales_number,
        after_sales_type,
        order_id,
        item_id,
        order_info[0]['product_name'],
        customer_id,
        user_info[0]['username'],
        user_info[0]['phone_number'],
        reason,
        reason,
        '待审核'
    )
    mysql_factory.execute_insert(insert_query, values)
    return f"售后申请成功,售后单号：{after_sales_number},请补充物流单号"



@tool
@timer_decorator
def add_tracking_number(after_sales_number: str, tracking_number: str) -> str:
    """添加物流单号。
    Args:
        after_sales_number (str): 售后单号
        tracking_number (str): 物流单号
    Returns:
        str: 添加物流单号结果
    """
    update_query = f"""
    UPDATE after_sales SET return_tracking_number = '{tracking_number}' 
    ,processing_status = '已完成' WHERE after_sales_number = '{after_sales_number}'
    """
    mysql_factory.execute_update(update_query)
    return f"物流单号添加成功"



@tool
@timer_decorator
def check_after_sales_status(after_sales_number: str) -> str:
    """检查售后单状态。
    Args:
        after_sales_number (str): 售后单号
    Returns:
        str: 售后单状态
    """
    query = f"""
    SELECT processing_status FROM after_sales WHERE after_sales_number = '{after_sales_number}'
    """
    status = mysql_factory.execute_query(query)
    return "售后单状态："+status[0]['processing_status']


after_sales_safe_tools = [after_sales_service,check_after_sales_status]
after_sales_sensitive_tools = [add_tracking_number]

if __name__ == "__main__":
    # print(after_sales_service.invoke({"customer_id": 1, "item_id": 485695, "order_number": 2025020811234135952947, "reason": "质量问题", "after_sales_type": "退货退款"}))
    # print(add_tracking_number.invoke({"after_sales_number": "202502081733166217", "tracking_number": "SF1234567890"}))
    print(check_after_sales_status.invoke({"after_sales_number": "202502081733166217"}))